package com.guodu.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.guodu.base.BaseDao;
import com.guodu.dao.SupplierRedTwoDao;
import com.guodu.entity.SupplierRedTwo;
import com.guodu.entity.SupplierRedTwoStatus;
import com.guodu.entity.Units;
import com.guodu.vo.SupplierRedTwoQueryVo;

@Repository("supplierRedTwoDao")
public class SupplierRedTwoDaoImpl extends BaseDao implements SupplierRedTwoDao{

	
	public int findSizeByVo(SupplierRedTwoQueryVo supplierRedTwoQueryVo,
			Units units) {
		StringBuffer hql = new StringBuffer("select count(*) from SupplierRedTwo s where 1=1");
		if(supplierRedTwoQueryVo != null){
			if(supplierRedTwoQueryVo.getYearStart() != null && !supplierRedTwoQueryVo.getYearStart().trim().equals("")){
				hql.append(" and s.supplierYear >="+supplierRedTwoQueryVo.getYearStart());
			}
			if(supplierRedTwoQueryVo.getYearEnd() != null && !supplierRedTwoQueryVo.getYearEnd().trim().equals("")){
				hql.append(" and s.supplierYear <="+supplierRedTwoQueryVo.getYearEnd());
			}
			if(supplierRedTwoQueryVo.getScopeSupply() != null && !supplierRedTwoQueryVo.getScopeSupply().trim().equals("")){
				hql.append(" and s.scopeSupply like '%"+supplierRedTwoQueryVo.getScopeSupply()+"%'");
			}
            if(supplierRedTwoQueryVo.getProducts() != null && !supplierRedTwoQueryVo.getProducts().trim().equals("")){
            	hql.append(" and s.products like '%"+supplierRedTwoQueryVo.getProducts()+"%'");
            }			
			if(supplierRedTwoQueryVo.getIndustry() != null && !supplierRedTwoQueryVo.getIndustry().trim().equals("")){
				hql.append(" and s.industry like '%"+supplierRedTwoQueryVo.getIndustry()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierName() != null && !supplierRedTwoQueryVo.getSupplierName().trim().equals("")){
				hql.append(" and s.supplierName like '%"+supplierRedTwoQueryVo.getSupplierName()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierCode() != null && !supplierRedTwoQueryVo.getSupplierCode().trim().equals("")){
				hql.append(" and s.supplierCode like '%"+supplierRedTwoQueryVo.getSupplierCode()+"%'");
			}
			if(supplierRedTwoQueryVo.getExamine() != null && !supplierRedTwoQueryVo.getExamine().toString().equals("")){
				hql.append(" and s.examine like '%"+supplierRedTwoQueryVo.getExamine()+"%'");
			}
			if(supplierRedTwoQueryVo.getCreateTimeStart() != null && !supplierRedTwoQueryVo.getCreateTimeStart().trim().equals("")){
				hql.append(" and s.createTime >= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getCreateTimeEnd() != null && !supplierRedTwoQueryVo.getCreateTimeEnd().trim().equals("")){
				hql.append(" and s.createTime <= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getFunitsId() != null){
				hql.append(" and (unitsId="+supplierRedTwoQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierRedTwoQueryVo.getFunitsId()+"))");
			}
			if(supplierRedTwoQueryVo.getUnitsId()!=null){
				hql.append(" and  unitsId = "+supplierRedTwoQueryVo.getUnitsId());
			}		
			if(supplierRedTwoQueryVo.getStatus()!=null&&supplierRedTwoQueryVo.getStatus().trim().length()>0&&!supplierRedTwoQueryVo.getStatus().equals("10")){
				hql.append(" and status ="+supplierRedTwoQueryVo.getStatus());
			}
			
		}	
//		if(units==null){
//			hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
//		}else
//		if(units.getUnitsLevel()==1L){
//			hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))) or (status =1 or status=3))");
//		}else{
//			hql.append(" and ((status =1 or status =3) or (unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
//		}
		if(units==null){
		    hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
	    }else if(units.getUnitsLevel()==1L){
		    hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))))");
	    }else{
//		    hql.append(" and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");	
	    	if(supplierRedTwoQueryVo.getUnitsId()!=null){
		    	hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and unitsId="+supplierRedTwoQueryVo.getUnitsId()+"))");	
	    	}else{
	    		hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and exists(select 1 from Units us where us.unitsId = s.unitsId and us.funitsId = "+supplierRedTwoQueryVo.getFunitsId()+")))");
	    	}
	    }						
		return super.findInt(hql.toString());
	}

	public List<SupplierRedTwo> findByVo(
			SupplierRedTwoQueryVo supplierRedTwoQueryVo, Units units,
			int curPage, int pageSize) {
		StringBuffer hql = new StringBuffer("from SupplierRedTwo s where 1=1");
        if(supplierRedTwoQueryVo != null){
        	if(supplierRedTwoQueryVo.getYearStart() != null && !supplierRedTwoQueryVo.getYearStart().trim().equals("")){
				hql.append(" and s.supplierYear >="+supplierRedTwoQueryVo.getYearStart());
			}
			if(supplierRedTwoQueryVo.getYearEnd() != null && !supplierRedTwoQueryVo.getYearEnd().trim().equals("")){
				hql.append(" and s.supplierYear <="+supplierRedTwoQueryVo.getYearEnd());
			}
			if(supplierRedTwoQueryVo.getScopeSupply() != null && !supplierRedTwoQueryVo.getScopeSupply().trim().equals("")){
				hql.append(" and s.scopeSupply like '%"+supplierRedTwoQueryVo.getScopeSupply()+"%'");
			}
            if(supplierRedTwoQueryVo.getProducts() != null && !supplierRedTwoQueryVo.getProducts().trim().equals("")){
            	hql.append(" and s.products like '%"+supplierRedTwoQueryVo.getProducts()+"%'");
            }	
			if(supplierRedTwoQueryVo.getIndustry() != null && !supplierRedTwoQueryVo.getIndustry().trim().equals("")){
				hql.append(" and s.industry like '%"+supplierRedTwoQueryVo.getIndustry()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierName() != null && !supplierRedTwoQueryVo.getSupplierName().trim().equals("")){
				hql.append(" and s.supplierName like '%"+supplierRedTwoQueryVo.getSupplierName()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierCode() != null && !supplierRedTwoQueryVo.getSupplierCode().trim().equals("")){
				hql.append(" and s.supplierCode like '%"+supplierRedTwoQueryVo.getSupplierCode()+"%'");
			}
			if(supplierRedTwoQueryVo.getExamine() != null && !supplierRedTwoQueryVo.getExamine().toString().equals("")){
				hql.append(" and s.examine like '%"+supplierRedTwoQueryVo.getExamine()+"%'");
			}
			if(supplierRedTwoQueryVo.getCreateTimeStart() != null && !supplierRedTwoQueryVo.getCreateTimeStart().trim().equals("")){
				hql.append(" and s.createTime >= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getCreateTimeEnd() != null && !supplierRedTwoQueryVo.getCreateTimeEnd().trim().equals("")){
				hql.append(" and s.createTime <= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getFunitsId() != null){
				hql.append(" and (unitsId="+supplierRedTwoQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierRedTwoQueryVo.getFunitsId()+"))");
			}
			if(supplierRedTwoQueryVo.getUnitsId()!=null){
				hql.append(" and  unitsId = "+supplierRedTwoQueryVo.getUnitsId());
			}	
			if(supplierRedTwoQueryVo.getStatus()!=null&&supplierRedTwoQueryVo.getStatus().trim().length()>0&&!supplierRedTwoQueryVo.getStatus().equals("10")){
				hql.append(" and status ="+supplierRedTwoQueryVo.getStatus());
			}
		}
//        if(units==null){
//			hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
//		}else
//		if(units.getUnitsLevel()==1L){
//			hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))) or (status =1 or status=3))");
//		}else{
//			hql.append(" and ((status =1 or status =3) or (unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
//		}
        if(units==null){
		    hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
	    }else if(units.getUnitsLevel()==1L){
		    hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))))");
	    }else{
//		    hql.append(" and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");	
	    	if(supplierRedTwoQueryVo.getUnitsId()!=null){
		    	hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and unitsId="+supplierRedTwoQueryVo.getUnitsId()+"))");	
	    	}else{
	    		hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and exists(select 1 from Units us where us.unitsId = s.unitsId and us.funitsId = "+supplierRedTwoQueryVo.getFunitsId()+")))");
	    	}
	    }	
        hql.append(" order by createTime desc");		
		return super.findPages(hql.toString(), curPage, pageSize);
	}
	
	@SuppressWarnings("unchecked")
	public List<SupplierRedTwo> downLoad(
			SupplierRedTwoQueryVo supplierRedTwoQueryVo,Units units) {
		StringBuffer hql = new StringBuffer("from SupplierRedTwo s where 1=1");
        if(supplierRedTwoQueryVo != null){
        	if(supplierRedTwoQueryVo.getYearStart() != null && !supplierRedTwoQueryVo.getYearStart().trim().equals("")){
				hql.append(" and s.supplierYear >="+supplierRedTwoQueryVo.getYearStart());
			}
			if(supplierRedTwoQueryVo.getYearEnd() != null && !supplierRedTwoQueryVo.getYearEnd().trim().equals("")){
				hql.append(" and s.supplierYear <="+supplierRedTwoQueryVo.getYearEnd());
			}
			if(supplierRedTwoQueryVo.getScopeSupply() != null && !supplierRedTwoQueryVo.getScopeSupply().trim().equals("")){
				hql.append(" and s.scopeSupply like '%"+supplierRedTwoQueryVo.getScopeSupply()+"%'");
			}
            if(supplierRedTwoQueryVo.getProducts() != null && !supplierRedTwoQueryVo.getProducts().trim().equals("")){
            	hql.append(" and s.products like '%"+supplierRedTwoQueryVo.getProducts()+"%'");
            }	
			if(supplierRedTwoQueryVo.getIndustry() != null && !supplierRedTwoQueryVo.getIndustry().trim().equals("")){
				hql.append(" and s.industry like '%"+supplierRedTwoQueryVo.getIndustry()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierName() != null && !supplierRedTwoQueryVo.getSupplierName().trim().equals("")){
				hql.append(" and s.supplierName like '%"+supplierRedTwoQueryVo.getSupplierName()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierCode() != null && !supplierRedTwoQueryVo.getSupplierCode().trim().equals("")){
				hql.append(" and s.supplierCode like '%"+supplierRedTwoQueryVo.getSupplierCode()+"%'");
			}
			if(supplierRedTwoQueryVo.getExamine() != null && !supplierRedTwoQueryVo.getExamine().toString().equals("")){
				hql.append(" and s.examine like '%"+supplierRedTwoQueryVo.getExamine()+"%'");
			}
			if(supplierRedTwoQueryVo.getCreateTimeStart() != null && !supplierRedTwoQueryVo.getCreateTimeStart().trim().equals("")){
				hql.append(" and s.createTime >= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getCreateTimeEnd() != null && !supplierRedTwoQueryVo.getCreateTimeEnd().trim().equals("")){
				hql.append(" and s.createTime <= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getFunitsId() != null){
				hql.append(" and (unitsId="+supplierRedTwoQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierRedTwoQueryVo.getFunitsId()+"))");
			}
			if(supplierRedTwoQueryVo.getUnitsId()!=null){
				hql.append(" and  unitsId = "+supplierRedTwoQueryVo.getUnitsId());
			}	
			if(supplierRedTwoQueryVo.getStatus()!=null&&supplierRedTwoQueryVo.getStatus().trim().length()>0&&!supplierRedTwoQueryVo.getStatus().equals("10")){
				hql.append(" and status ="+supplierRedTwoQueryVo.getStatus());
			}
		}
//        if(units==null){
//			hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
//		}else
//		if(units.getUnitsLevel()==1L){
//			hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))) or (status =1 or status=3))");
//		}else{
//			hql.append(" and ((status =1 or status =3) or (unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
//		}
        if(units==null){
		    hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
	    }else if(units.getUnitsLevel()==1L){
		    hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))))");
	    }else{
		    hql.append(" and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
	    }		
		hql.append(" order by createTime desc");			
		return super.find(hql.toString());
	}
	
	public void examinAll(SupplierRedTwoQueryVo supplierRedTwoQueryVo,
			Units units) {
		StringBuffer hql = new StringBuffer();
		if(units == null){
			hql.append("update from SupplierRedTwo s set s.status=3 where s.status=0 ");			
		}else if(units.getUnitsLevel()==1L){
			hql.append("update from SupplierRedTwo s set s.status=1 where s.status=0 ");
		}
		if(supplierRedTwoQueryVo != null){
        	if(supplierRedTwoQueryVo.getYearStart() != null && !supplierRedTwoQueryVo.getYearStart().trim().equals("")){
				hql.append(" and s.supplierYear >="+supplierRedTwoQueryVo.getYearStart());
			}
			if(supplierRedTwoQueryVo.getYearEnd() != null && !supplierRedTwoQueryVo.getYearEnd().trim().equals("")){
				hql.append(" and s.supplierYear <="+supplierRedTwoQueryVo.getYearEnd());
			}
			if(supplierRedTwoQueryVo.getScopeSupply() != null && !supplierRedTwoQueryVo.getScopeSupply().trim().equals("")){
				hql.append(" and s.scopeSupply like '%"+supplierRedTwoQueryVo.getScopeSupply()+"%'");
			}
            if(supplierRedTwoQueryVo.getProducts() != null && !supplierRedTwoQueryVo.getProducts().trim().equals("")){
            	hql.append(" and s.products like '%"+supplierRedTwoQueryVo.getProducts()+"%'");
            }	
			if(supplierRedTwoQueryVo.getIndustry() != null && !supplierRedTwoQueryVo.getIndustry().trim().equals("")){
				hql.append(" and s.industry like '%"+supplierRedTwoQueryVo.getIndustry()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierName() != null && !supplierRedTwoQueryVo.getSupplierName().trim().equals("")){
				hql.append(" and s.supplierName like '%"+supplierRedTwoQueryVo.getSupplierName()+"%'");
			}
			if(supplierRedTwoQueryVo.getSupplierCode() != null && !supplierRedTwoQueryVo.getSupplierCode().trim().equals("")){
				hql.append(" and s.supplierCode like '%"+supplierRedTwoQueryVo.getSupplierCode()+"%'");
			}
			if(supplierRedTwoQueryVo.getExamine() != null && !supplierRedTwoQueryVo.getExamine().toString().equals("")){
				hql.append(" and s.examine like '%"+supplierRedTwoQueryVo.getExamine()+"%'");
			}
			if(supplierRedTwoQueryVo.getCreateTimeStart() != null && !supplierRedTwoQueryVo.getCreateTimeStart().trim().equals("")){
				hql.append(" and s.createTime >= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getCreateTimeEnd() != null && !supplierRedTwoQueryVo.getCreateTimeEnd().trim().equals("")){
				hql.append(" and s.createTime <= TO_DATE('"+supplierRedTwoQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierRedTwoQueryVo.getFunitsId() != null){
				hql.append(" and (unitsId="+supplierRedTwoQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierRedTwoQueryVo.getFunitsId()+"))");
			}
			if(supplierRedTwoQueryVo.getUnitsId()!=null){
				hql.append(" and  unitsId = "+supplierRedTwoQueryVo.getUnitsId());
			}	
			if(supplierRedTwoQueryVo.getStatus()!=null&&supplierRedTwoQueryVo.getStatus().trim().length()>0&&!supplierRedTwoQueryVo.getStatus().equals("10")){
				hql.append(" and status ="+supplierRedTwoQueryVo.getStatus());
			}
		}
        if(units==null){
		    hql.append(" and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
	    }else if(units.getUnitsLevel()==1L){
		    hql.append(" and (exists(select 1 from UserInfo u where s.createPeople = u.userId and ( u.unitsId="+units.getUnitsId()+" or exists(select 1 from Units n where n.funitsId="+units.getUnitsId()+" and n.unitsId = u.unitsId))))");
	    }else{
		    hql.append(" and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
	    }		
        super.executeUpdate(hql.toString());		
	}

	public void save(SupplierRedTwo supplierRedTwo) {
		super.save(supplierRedTwo);
	}

	public void examine(String ids, Long status) {
		StringBuffer hql = new StringBuffer("update SupplierRedTwo set status =");
		hql.append(status);
		hql.append(" where supplierId in (");
		hql.append(ids);
		hql.append(")");
		super.executeUpdate(hql.toString());
		
	}

	public void delByIds(String[] ids) {
		super.deleteAllById(SupplierRedTwo.class,ids);
		
	}
	public SupplierRedTwo findById(String id){
		return (SupplierRedTwo) super.findById(SupplierRedTwo.class,id);
	}

	
	public void update(SupplierRedTwo supplierRedTwo) {
		super.update(supplierRedTwo);
		
	}

	public void changeStatus(List<SupplierRedTwoStatus> supplierRedTwoStatusList) {
		super.saveAll(supplierRedTwoStatusList);
		
	}

	
	public List<SupplierRedTwoStatus> findStatus(String id) {
		
		return super.findByProperty(SupplierRedTwoStatus.class,"supplierId",id);
	}

	public void deleteById(String id, String fileUrl) {
		
		super.delete(SupplierRedTwo.class, id);
	}

	

	
	
	
	
	
	

}
